{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "请输入文件所在目录，如E:/工作进度/wind/,按回车结束输入： E:/工作进度/test/\n",
      "请输入省份，如安徽省,按回车结束输入： 安徽省\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame\n",
    "\n",
    "def get_data(path,province):\n",
    "    table_name=province+'通用表.xls'\n",
    "    if not os.path.exists(path+province+'通用表.xls'):\n",
    "        table_name=province+'通用表.xlsx'   \n",
    "    format_data=pd.read_excel(path+table_name,sheet_name=None)\n",
    "    file_list=os.listdir(path)\n",
    "    file_list.remove(table_name)\n",
    "    unformat_data=pd.DataFrame(columns=[])\n",
    "    for file in file_list:\n",
    "        table_path=path+file\n",
    "        tep_data=pd.read_excel(table_path)\n",
    "        length=tep_data.shape[0]\n",
    "        tep_data=tep_data[2:length-2]\n",
    "        tep_data['指标名称']=tep_data['指标名称'].dt.strftime('%Y')\n",
    "        tep_data=tep_data.set_index('指标名称')\n",
    "        tep_data.index.names = ['年份']\n",
    "        unformat_data=pd.concat([unformat_data,tep_data],axis=1)\n",
    "    return format_data,unformat_data\n",
    "        \n",
    "        \n",
    "\n",
    "def get_column_name(i):\n",
    "    i=i.split('（',1)\n",
    "    i=i[0]\n",
    "    i=i.split('(',1)\n",
    "    i=i[0]\n",
    "    return i\n",
    "def get_split_column_name(i):\n",
    "    i=i.split(':')\n",
    "    return i\n",
    "\n",
    "def condition_judge(c,d):\n",
    "    if get_column_name(c) =='GDP' and get_column_name(c) in get_split_column_name(d) :\n",
    "        a=['第一产业','第二产业','第三产业']\n",
    "        for i in a :\n",
    "            if i in get_split_column_name(d):\n",
    "                return False\n",
    "        return True\n",
    "    elif get_column_name(c) in get_split_column_name(d) :\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "                \n",
    "            \n",
    "    \n",
    "path=input(\"请输入文件所在目录，如E:/工作进度/wind/,按回车结束输入：\")\n",
    "province=input(\"请输入省份，如安徽省,按回车结束输入：\")\n",
    "if  os.path.exists(path+province+'最终表.xlsx'):\n",
    "    os.remove(path+province+'最终表.xlsx')\n",
    "total_format_data,unformat_data=get_data(path=path,province=province)\n",
    "citys=list(total_format_data.keys())\n",
    "for i in citys:\n",
    "    format_data=DataFrame(total_format_data[i])\n",
    "    format_data['年份']=format_data['年份'].astype(str)\n",
    "    location=format_data['地区'].map(lambda s: s.strip()).to_list()\n",
    "    year=format_data['年份'].map(lambda s:  s.strip()).to_list()\n",
    "    tem_index=zip(location,year)\n",
    "    index_name=[]\n",
    "    for j in tem_index :\n",
    "        a=j[0]\n",
    "        b=j[1]\n",
    "        index_name.append(a+b)\n",
    "    format_data['index']=index_name\n",
    "    format_data=format_data.set_index('index')\n",
    "    columns_name=format_data.columns.values.tolist()\n",
    "    un_index_name=unformat_data._stat_axis.values.tolist()\n",
    "    un_columns_name=unformat_data.columns.values.tolist()\n",
    "    for a in index_name:\n",
    "        for b in un_index_name:\n",
    "            if a[-4:]==b:\n",
    "                for c in columns_name:\n",
    "                    for d in un_columns_name:\n",
    "                        if a[0:len(a)-4] in get_split_column_name(d) and condition_judge(c,d):\n",
    "                            format_data.loc[a,c]=unformat_data.loc[b,d]\n",
    "                break\n",
    "    format_data['地区']=format_data['地区'].map(str.strip)\n",
    "    if not os.path.exists(path+province+'最终表.xlsx'):\n",
    "        format_data.to_excel(path+province+'最终表.xlsx', sheet_name=i,index=False)\n",
    "    else:    \n",
    "        with pd.ExcelWriter(path+province+'最终表.xlsx',mode='a',engine='openpyxl') as writer:  \n",
    "            format_data.to_excel(writer, sheet_name=i,index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
